PostgreSQL Autovacuum

log_autovacuum_min_duration

1 Autovacuum 执行的工作

1. Autovacuum;
2. Analyze

2 Autovacuum vacuum 触发条件

AutovacuumVACUUM thresoldfor a table =
autovacuum_vacuum_scale_factor* number of tuples + autovacuum_vacuum_threshold

3 Autovacuum Analyze 触发条件

AutovacuumANALYZE threshold for a table =
autovacuum_analyze_scale_factor* number of tuples + autovacuum_analyze_threshold

4 Autovacuum 调优

4.1 针对一张表Autovacuum 调优

4.1.1 判断这张表是否需要进行调优

SELECT n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as "deletes", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples"
FROM pg_stat_user_tables
WHERE schemaname= 'public' and relname= 'payment';

4.1.2 单独配置表级的清理参数

ALTER TABLE payment SET (AUTOVACUUM_VACUUM_THRESHOLD = 100);
ALTER TABLE payment SET (AUTOVACUUM_VACUUM_SCALE_FACTOR = 0);
ALTER TABLE payment SET (AUTOVACUUM_VACUUM_COST_DELAY = 10);
\d+ public.payment

4.2 Autovacuum 参数调优

4.2.1 autovacuum 进程数量限制

在可能包含多个数据库的实例/群集上，一次运行的autovacuum进程数不能超过下面参数设置的值：

autovacuum_max_workers = 3 (Default)

4.2.2 Autovacuum 进程启动时间限制。

启动下一个autovacuum之前的等待时间：

autovacuum_naptime= 1min

其中N是实例中数据库的总数

4.2.3 Autovacuum 总成本限制

autovacuum_vacuum_cost_limit:
autovacuum可达到的总成本限制（结合所有autovacuum作业）（3 个work 总共不能超过多少值）。
默认值

autovacuum_vacuum_cost_limit= -1 

4.2.4 Autovacuum 总成本限制方法

autovacuum_vacuum_cost_delay:
当一个清理工作达到autovacuum_vacuum_cost_limit指定的成本限制时，autovacuum将休眠数毫秒
默认值

autovacuum_vacuum_cost_delay= 20ms

4.2.5 Autovacuum 成本设置

vacuum_cost_page_hit:

读取已在共享缓冲区中且不需要磁盘读取的页的成本.
默认值

vacuum_cost_page_hit= 1

vacuum_cost_page_miss:
获取不在共享缓冲区中的页的成本.
默认值

vacuum_cost_page_miss= 10

vacuum_cost_page_dirty:
在每一页中发现死元组时写入该页的成本.
默认值

vacuum_cost_page_dirty= 20

4.3 Autovacuum 性能计算

在读取延迟为0毫秒的最佳情况下，autovacuum可以唤醒并进入睡眠50次（1000毫秒/20毫秒），因为唤醒之间的延迟需要20毫秒。
1 second = 1000 milliseconds = 50 * autovacuum_vacuum_cost_delay
由于在共享缓冲区中每次读取一个页面的相关成本是1，因此在每个唤醒中可以读取200个页面（因为上面把总成本限制设置为200），在50个唤醒中可以读取50*200个页面。

4.3.1 计算内存中的读取速度

如果在共享缓冲区中找到了所有具有死元组的页，并且autovacuum代价延迟为20毫秒，则它可以在每一轮中读取：（（200/ vacuum_cost_page_hit）*8）KB，这需要等待autovacuum代价延迟时间量。

4.3.2 计算磁盘中的读取速度

因此，考虑到块大小为8192字节，autovacuum最多可以读取：50*200*8kb=78.13mb/s（如果在共享缓冲区中已经找到块）

4.3.3 写入磁盘的速度

如果块不在共享缓冲区中，需要从磁盘提取，则autovacuum可以读取：50*（200/ vacuum_cost_page_miss）*8）KB=7.81 MB/秒。
现在，为了从页/块中删除死元组，写操作的开销是：vacuum_cost_page_dirty，默认设置为20
一个auto vacuum每秒最多可以写/脏：50*（200/ vacuum_cost_page_dirty）*8）KB=3.9mb/秒。

4.4 提高Autovacuum 清理速度

autovacuum_max_workers 
autovacuum_vacuum_cost_limit

5 附录

5.1 死亡元组查询

SELECT *,
n_dead_tup > av_threshold AS "av_needed",
CASE WHEN reltuples > 0
THEN round(100.0 * n_dead_tup / (reltuples))
ELSE 0
END
AS pct_dead
FROM
(SELECT
N.nspname,
C.relname,
sys_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
sys_stat_get_tuples_updated(C.oid) AS n_tup_upd,
sys_stat_get_tuples_deleted(C.oid) AS n_tup_del,
sys_stat_get_live_tuples(C.oid) AS n_live_tup,
sys_stat_get_dead_tuples(C.oid) AS n_dead_tup,
C.reltuples AS reltuples,
round(current_setting('autovacuum_vacuum_threshold')::integer
+ current_setting('autovacuum_vacuum_scale_factor')::numeric *
C.reltuples)
AS av_threshold,
date_trunc('minute',greatest(sys_stat_get_last_vacuum_time(C.oid),
sys_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum,
date_trunc('minute',greatest(sys_stat_get_last_analyze_time(C.oid),
sys_stat_get_last_analyze_time(C.oid))) AS last_analyze
FROM sys_class C
LEFT JOIN sys_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN ('r', 't')
AND N.nspname IN ('public') 
AND N.nspname !~ '^sys_toast'
AND c.relname IN ('kbbench_accounts')
) AS av
ORDER BY av_needed DESC,n_dead_tup DESC;